//
// Created by Felix on 24-4-24.
//

#ifndef GAZE_USERDB_HPP
#define GAZE_USERDB_HPP

#include "oatpp-sqlite/orm.hpp"

#include "model/UserModel.hpp"

#include OATPP_CODEGEN_BEGIN(DbClient)

class UserDb : public oatpp::orm::DbClient {
public:
    explicit UserDb(
            const std::shared_ptr<oatpp::orm::Executor> &executor
    ) : oatpp::orm::DbClient(executor) {
        oatpp::orm::SchemaMigration migration(executor);
        auto version = executor->getSchemaVersion();
        OATPP_LOGD("UserDb", "Migration - OK. Version=%lld.", version)
    }

    QUERY(createUser,
          "INSERT INTO user "
          "(username, password, role, status, create_time, update_time, delete_time) VALUES "
          "(:user.username, :user.password, :user.role, :user.status, :user.create_time, :user.update_time, :user.delete_time) "
          "RETURNING *;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::Object<UserModel>, user))

    QUERY(updateUserDetails,
          "UPDATE user SET "
          "username = :user.username, "
          "role = :user.role, "
          "status = :user.status, "
          "update_time = :user.update_time "
          "where id = :user.id "
          "RETURNING *;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::Object<UserModel>, user))

    QUERY(updateUserStatus,
          "UPDATE user SET "
          "status = :user.status, "
          "update_time = :user.update_time "
          "where id = :user.id "
          "RETURNING *;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::Object<UserModel>, user))

    QUERY(updateUserPassword,
          "UPDATE user SET "
          "password = :user.password, "
          "update_time = :user.update_time "
          "where id = :user.id "
          "RETURNING *;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::Object<UserModel>, user))

    QUERY(authenticateUser,
          "SELECT id FROM user "
          "WHERE username = :username AND password = :password;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::String, username),
          PARAM(oatpp::String, password))

    QUERY(getUserById,
          "SELECT * FROM user "
          "WHERE id = :user_id;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::Int32, userId, "user_id"))

    QUERY(getUserByUsername,
          "SELECT * FROM user "
          "WHERE username = :username;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::String, username, "username"))

    QUERY(getUserByUsernameFilterOutUserId,
          "SELECT * FROM user "
          "WHERE username = :username and id <> :user_id;",
          PREPARE(true), // prepared statement!
          PARAM(oatpp::String, username),
          PARAM(oatpp::Int32, userId, "user_id"))

    QUERY(getAllUsers,
          "SELECT id as user_id, username, role, status, create_time, update_time, delete_time FROM user "
          "WHERE delete_time = 0 "
          "ORDER BY id DESC "
          "LIMIT :limit OFFSET :offset;",
          PARAM(oatpp::Int32, offset),
          PARAM(oatpp::Int32, limit))

    QUERY(countAllUsers, "SELECT COUNT(id) FROM user where 1;")
};

#include OATPP_CODEGEN_END(DbClient)

#endif //GAZE_USERDB_HPP
